package nullby.omnia.db;

import java.io.File;
import java.util.ArrayList;

import nullby.omnia.model.WordOfVoc;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.os.Environment;
import android.text.TextUtils;
import android.util.AndroidRuntimeException;

public class ExternalDatabaseHelper {

	private File dbFile;
	private SQLiteDatabase db;
	private CursorFactory factory;

	private static ExternalDatabaseHelper instance;

	public static ExternalDatabaseHelper get() {
		return instance;
	}

	public static ExternalDatabaseHelper newInstanse(String fileNameDb) {
		if (instance == null) {
			instance = new ExternalDatabaseHelper(fileNameDb);
		}
		return instance;
	}

	public ExternalDatabaseHelper(String fileNameDb) {
		if (!Environment.getExternalStorageState().equals(
				Environment.MEDIA_MOUNTED)) {
			throw new AndroidRuntimeException(
					"External storage (SD-Card) not mounted");
		}
		File appDbDir = new File(Environment.getExternalStorageDirectory(), "");
		if (!appDbDir.exists()) {
			appDbDir.mkdirs();
		}
		this.dbFile = new File(appDbDir, fileNameDb);
	}

	public boolean databaseFileExists() {
		return dbFile.exists();
	}

	private void open() {
		if (dbFile.exists()) {
			db = SQLiteDatabase.openDatabase(dbFile.getAbsolutePath(), factory,
					SQLiteDatabase.OPEN_READONLY);
		}
	}

	public void close() {
		if (db != null) {
			db.close();
			db = null;
		}
	}

	public SQLiteDatabase getReadableDatabase() {
		return getDatabase();
	}

	private SQLiteDatabase getDatabase() {
		if (db == null) {
			open();
		}
		return db;
	}

	public WordOfVoc getWordById(String id, SQLiteDatabase db)
			throws SQLException {
		WordOfVoc word = null;

		String query = String
				.format("SELECT docid, c0word, c1card FROM whd_entries_content WHERE docid == '%s'",
						id);

		Cursor cursor = db.rawQuery(query, null);

		if (cursor.moveToFirst()) {
			do {
				word = new WordOfVoc(cursor.getString(0), cursor.getString(1),
						cursor.getString(2));
			} while (cursor.moveToNext());
		}

		cursor.close();
		db.close();
		return word;
	}

	public ArrayList<WordOfVoc> getListWord(String word, SQLiteDatabase db)
			throws SQLException {
		ArrayList<WordOfVoc> words = new ArrayList<WordOfVoc>();

		if (!TextUtils.isEmpty(word)) {
			String query = String
					.format("SELECT docid, c0word FROM whd_entries_content WHERE c0word LIKE '%%%s%%'",
							word.trim());

			Cursor cursor = db.rawQuery(query, null);

			if (cursor.moveToFirst()) {
				do {
					words.add(new WordOfVoc(cursor.getString(0), cursor
							.getString(1)));
				} while (cursor.moveToNext());
			}

			cursor.close();
			db.close();
		}
		return words;
	}
}
